import os
import random

import pandas as pd
from sqlalchemy import create_engine

labels = [
    "精品小说", "历史", "文学", "艺术", "人物传记", "哲学宗教", "计算机", "心理", "社会文化", "个人成长", "经济理财", "政治军事", "童书", "教育学习", "科学科技", "生活百科"
]

import json

keys = ['title', 'author', 'cover', 'intro']
engine = create_engine('mysql+pymysql://huanyushi:123456@82.156.102.187/db_library?charset=utf8')
for i in range(1, 17):
    dataframe = []
    with open(f"data/{i * 100}.txt") as fp:
        for line in fp.readlines():
            books = json.loads(line)["books"]
            for book in books:
                info = book["bookInfo"]
                temp = {}
                for key in keys:
                    temp[key] = info[key]
                temp["category_id"] = i
                temp["number"] = random.randint(10, 300)
                dataframe.append(temp)
    # .to_sql("book_info", engine, index=False, if_exists='append')
    pd.DataFrame(dataframe).to_sql("book_info", engine, index=False, if_exists='append')
